using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Core;
using IglooCoder.Commons.WcfNhibernate;
using NHibernate;
using NHibernate.Linq;

namespace Infrastucture
{
    public class MutasiPiutangRepository : BaseNHibernateRepository<MutasiPiutang>
    {
        public MutasiPiutangRepository(ISessionStorage sessionStorage)
            : base(sessionStorage)
        {

        }

        public void DeleteMutasiPiutang(string ReferenceNumber)
        {

            ISQLQuery command =
                Session.CreateSQLQuery("Delete from MutasiPiutang Where ReferenceNumber='" + ReferenceNumber + "'");
            int res = command.ExecuteUpdate();
        }

        public void DeleteMutasiPiutangByCoa(string coa)
        {

            ISQLQuery command =
                Session.CreateSQLQuery("Delete from MutasiPiutang Where Coa='" + coa + "'");
            int res = command.ExecuteUpdate();
        }


        public MutasiPiutang FindLastMutasi(string customerId, string invoiceNo, DateTime tglMutasi)
        {
            var mutasiPiutang =
                Session.Linq<MutasiPiutang>().Where(x => x.CustomerId == customerId && x.InvoiceNo == invoiceNo && x.Tanggal <= tglMutasi)
                    .OrderByDescending(
                        x => x.Tanggal).FirstOrDefault();
            if (mutasiPiutang == null)
                mutasiPiutang = new MutasiPiutang();
            return mutasiPiutang;
        }


        public List<MutasiPiutang> GetPiutangBelumLunas()
        {
           StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM MutasiPiutang mp  ");
            sb.Append("WHERE mp.Tanggal=(SELECT MAX(Tanggal) FROM MutasiPiutang mp2 WHERE mp2.CustomerId = mp.CustomerId ");
            sb.Append("AND mp2.InvoiceNo = mp.InvoiceNo) AND mp.SaldoSisa > 0 ");
            var query = Session.CreateSQLQuery(sb.ToString()).AddEntity(typeof (MutasiPiutang));
            return query.List<MutasiPiutang>().ToList();
        }

        public List<MutasiPiutang> GetPiutangBelumLunas(string customerId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT * FROM MutasiPiutang mp  ");
            sb.Append("WHERE mp.Tanggal=(SELECT MAX(Tanggal) FROM MutasiPiutang mp2 WHERE mp2.CustomerId = mp.CustomerId ");
            sb.AppendFormat("AND mp2.InvoiceNo = mp.InvoiceNo) AND mp.SaldoSisa > 0 AND mp.CustomerId='{0}' ",customerId);
            var query = Session.CreateSQLQuery(sb.ToString()).AddEntity(typeof(MutasiPiutang));
            return query.List<MutasiPiutang>().ToList();
        }  

    }
}
